#!/usr/bin/env python
# -*- coding: utf-8 -*-
# SQL type wrappers audited by Joshua Kugler, 2008-02-20
"""
The tmpo_report table holds all the data pertaining to programmatically 
generating custom SQL reports. This API manages all access to or 
modification of that table. 

    +----------------------+-----------------+--------------------------+
    |     Column Name      |       Type      |       Description        |
    +----------------------+-----------------+--------------------------+
    | id                   | int,pk,identity | Unique record identifier |
    | title                | varchar         | Report title             |
    | description          | varchar         | Report description       |
    | query_values         | varchar         | For future use           |
    | query                | varchar         | Database query as SQL    |
    | active               | bit             | logical delete flag      |
    +----------------------+-----------------+--------------------------+

The functions found in this API are:

 get_all
   this function retrieves all the stored queries from the database, no filter

 delete_report
   this function checks the database for the existence of a given report id,
   and upon finding it flags it as 'inactive' - logical delete, using the 
   id number for the filter - returns a 404 if no such report exists.

 update_report
   function selects the original report out of the database, using the report
   id as the filter. The updated data is checked to make sure that only select
   statements are saved, and the record is updated.If the update query is 
   anything other than a select statement, a 409 (conflict) response is 
   generated If the original select comes back with no rows, then a 404 is 
   returned instead.

 add_report
   function reads the submitted form, checking to make sure that the query
   only contains select statements. returns 409 (conflict) response if anything
   other than a select statement.
"""
from string import Template
import re
##from tack.db import query, sql_string, sql_int, sql_unstring
from tack.db import sql_string, sql_int, sql_unstring
from tack.lib import string_app, content_headers, status_codes, json_app
from tack_tmpo.api.schedule_tools import build_row_dict

sql_get_all = """
SELECT
    * 
FROM
    tmpo_report
WHERE
    active = 1
ORDER BY id
"""

sql_get_one = """
SELECT
    * 
FROM
    tmpo_report
WHERE
    id = $report_id
    AND active = 1
"""

sql_set_to_inactive = """
UPDATE
    tmpo_report
SET
    active = 0
WHERE
    id = $report_id
"""

sql_update = """
UPDATE
    tmpo_report
SET
    title = $title,
    description = $description,
    query = $query 
WHERE
    id = $report_id
    AND active = 1
"""

sql_insert = """
INSERT INTO
    tmpo_report(
      title,
      description,
      query)
VALUES(
    $title,
    $description,
    $query)
"""

def get_all(environ, start_response):
    sql = sql_get_all
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    output = {}
    report_list = []
    for row in rows:
        row_dict = build_row_dict(cursor.description, row)
        row_dict['title'] = sql_unstring(row_dict['title'])
        row_dict['description'] = sql_unstring(row_dict['description'])
        row_dict['query'] = sql_unstring(row_dict['query'])
        report_list.append(row_dict)
    output['report'] = report_list
    return json_app(output)(environ, start_response)

def delete_report(environ, start_response):
    report_id = environ['dispatcher.groups']['report_id']
    sql = Template(sql_get_one).substitute(locals())
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    if rows:
        sql = Template(sql_set_to_inactive).substitute(locals())
        cursor.execute(sql)
        environ['connection'].commit()
        output = 'Deleted'
    else:
        start_response(status_codes.not_found, content_headers.default_plain())
        return ['404 Not Found']
    return string_app(output)(environ, start_response)

def update_report(environ, start_response):
    report_id = environ['dispatcher.groups']['report_id']
    sql = Template(sql_get_one).substitute(locals())
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    if rows:
        form = environ['form']
        title = sql_string(form['title'])
        description = sql_string(form['description'])
        query = sql_string(form['query'])
        if re.search('INSERT|SELECT INTO|DELETE|CREATE|DROP', query, re.I):
            start_response(status_codes.conflict, content_headers.default_plain())
            return ['409 Conflict']
        sql = Template(sql_update).substitute(locals())
        cursor.execute(sql)
        environ['connection'].commit()
        output = 'Updated'
    else:
        start_response(status_codes.not_found, content_headers.default_plain())
        return ['404 Not Found']
    return string_app(output)(environ, start_response)

def add_report(environ, start_response):
    form = environ['form']
    title = sql_string(form['title'])
    description = sql_string(form['description'])
    query = sql_string(form['query'])
    if re.search('INSERT|SELECT INTO|DELETE|CREATE|DROP', query, re.I):
        start_response(status_codes.conflict, content_headers.default_plain())
        return ['409 Conflict']
    sql = Template(sql_insert).substitute(locals())
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    environ['connection'].commit()
    output = 'Added'
    return string_app(output)(environ, start_response)

